package model;

import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

// 用于操作Group的数据库
public class GroupDAO {

    //    增加小组
    public boolean addGroup(Group group){
        try(Connection connection = DBUtil.getConnection()){
            String sql = "INSERT INTO groups (name) VALUES (?)";
            try(PreparedStatement preparedStatement = connection.prepareStatement(sql)){
                preparedStatement.setString(1,group.getName());
                int update = preparedStatement.executeUpdate();
                return update == 1;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return false;
    }

    //    删除小组 检查小组人数是否为0再删除
    public boolean deleteGroup(Group group) {
        // 1. 先查询小组里面人数是否为0
        try (Connection connection = DBUtil.getConnection()) {
            String sqlCheck = "SELECT COUNT(*) AS count FROM users WHERE group_id = ?";
            try (PreparedStatement statement = connection.prepareStatement(sqlCheck)) {
                statement.setInt(1, group.getId());
                try (ResultSet resultSet = statement.executeQuery()) {
                    if (resultSet.next() && resultSet.getInt("count") > 0) {
                        return false;
                    }
                }
            }

            // 2. 删除小组
            // 因为先前已经建立数据库连接了 所以此处无需再连接数据库了
            String deleteSql = "DELETE FROM groups WHERE id = ?";
            try (PreparedStatement deleteStatement = connection.prepareStatement(deleteSql)) {
                deleteStatement.setInt(1, group.getId());
                int update = deleteStatement.executeUpdate();
                return update == 1;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //    修改小组
    public boolean updateGroup(Group group){
        try(Connection connection = DBUtil.getConnection()){
//            由于 id 为自增主键 所以 id 不需要修改
            String updateSql = "update groups set name = ? where id = ?";
            try(PreparedStatement updateStatment = connection.prepareStatement(updateSql)){
                updateStatment.setString(1,group.getName());
                updateStatment.setInt(2,group.getId());
                int update = updateStatment.executeUpdate();
                return update == 1;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return false;
    }

    //    查询小组 根据id来查找小组
//    查询到小组 并返回所有属于该小组的成员
    public Group getGroupById(int id){
        Group group = null;
        try(Connection connection = DBUtil.getConnection()){
            String selectSql = "select * from groups where id = ?";
            try(PreparedStatement selectStatment = connection.prepareStatement(selectSql)){
                selectStatment.setInt(1,id);// 设置SQL 查询语句中的参数

                try(ResultSet resultSetGroup = selectStatment.executeQuery()){
                    if (resultSetGroup.next()){
                        group = new Group(); //  创建 Group 对象
                        group.setId(resultSetGroup.getInt("id")); // 从结果集中读取 ID 字段的值
                        group.setName(resultSetGroup.getString("name")); // 设置 Group 对象的名称

//                        查询该小组的成员信息
                        List<User> members = new ArrayList<>();
                        String selectMemberSql = "select * from users where group_id = ?";
                        try(PreparedStatement selectMemberStatment = connection.prepareStatement(selectMemberSql)){
                            selectMemberStatment.setInt(1,id);
                            try(ResultSet resultSetMembers = selectMemberStatment.executeQuery()){
                                while(resultSetMembers.next()){ // 遍历结果集合的每一行
                                    User user = new User();
                                    user.setId(resultSetMembers.getInt("id"));
                                    user.setName(resultSetMembers.getString("name"));
                                    user.setPassword(resultSetMembers.getString("password"));
                                    user.setGroup_id(resultSetMembers.getInt("group_id"));
                                    members.add(user); // 将用户添加到成员列表中
                                }
                            }
                        }
                        group.setMembers(members);//添加成员列表
                    }
                }
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return group; // 返回 Group 对象或 null
    }

}
